**NOTE: Change text inside bracets of [folder] to your folder location.

**by month
**Excess Deaths
import delimited "HSSC replication data\Public data\reference_hospitalization_all_locs.csv", clear
gen time=date(date,"YMD")
gen time_string=time
format time_string %td
tab time_string 
gen year=year(time)
gen month=month(time)
gen day=day(time)
tab year
tab day


gen ratio_actual_report=totdea_mean /totdea_reported_mean

sum ratio_actual_repor totdea_reported_mean totdea_mean 

ren totdea_reported_mean_p100k_rate totdea_reported_mean_p100k

foreach x in total_pop totdea_mean_p100k_rate  totdea_reported_mean_p100k seroprev_mean mobility_composite ///
ratio_actual_report totdea_mean totdea_reported_mean {
ren `x' IHME`x'
}

ren location_name countryname
ren location_id ihme_id

merge m:1 countryname using "HSSC replication data\Public data\ihme_country_codes.dta", update
replace countrycode="CIV" if ihme_id==205
replace countrycode="YEM" if ihme==157
drop if _merge==2
drop _merge
drop if ihme==35
encode countrycode, gen(id)
drop if id==.


ren IHMEseroprev_mean IHME_sero
ren IHMEtotdea_mean_p100k_rate IHME_est_deaths
ren IHMEtotdea_reported_mean_p100k IHME_reported_deaths


gen obs=1
tsset id time
foreach x in IHME_sero IHME_est_deaths IHME_reported_deaths {
by id: gen RTM`x'=sum(`x')
by id: gen RTN`x'=sum(obs) 
gen RT`x'=RTM`x'/RTN`x'
drop RTM`x' RTN`x'
}

collapse (first) ihme_id countryname (mean) IHME_sero IHME_est_deaths IHME_reported_deaths RTIHME_sero RTIHME_est_deaths RTIHME_reported_deaths, by(countrycode month year)

save "HSSC replication data\Public data\IHME_excess_deaths_monthly_cum_to_date.dta", replace

